package edu.tms.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import edu.tms.bean.ClassActivity;
import edu.tms.crud.JdbcUtil;
import edu.tms.dao.common.CommnonDao;

/**
 * 班级活动
 * @author Administrator
 * 
 */
public class ClassActivityDaoImp implements CommnonDao<ClassActivity>{
	/**
	 * 保存内容
	 * @param value
	 * @throws Exception
	 */
	@Override
	public long save(ClassActivity value) throws Exception {
		String sql="insert into t_class_activity(activity_title,album_id,class_id,activity_content,create_time,deadline)"
				+ " value (?,?,?,?,?,?)";
		ClassActivity ca=(ClassActivity)value;
		return JdbcUtil.insertAuto(sql,
					ca.getActivityTitle(),
					ca.getAlbumId(),
					ca.getClassId(),
					ca.getActivityContent(),
					ca.getCreateTime(),
					ca.getDeadline());
	}
	/**
	 * 删除内容
	 * 相册表t_album，班级表t_class
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int update(ClassActivity value) throws Exception {	
		String sql="update t_class_activity set album_id=?,class_id=?, activity_content=?,"
				+ "create_time=?,exhibition_state=? where class_activity_id=?";
		ClassActivity ca=(ClassActivity)value;
		return JdbcUtil.update(sql,
				ca.getAlbumId(),
				ca.getClassId(),
				ca.getActivityContent(),
				ca.getCreateTime(),
				ca.getClassActivityId());
	}
	
	/**
	 * 删除内容
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int delete(ClassActivity value) throws Exception {
		return 0;
	}
	
	/**
	 * 查找所有的内容
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<ClassActivity> findAll(int pageNo, int pageSize) throws Exception {
		String sql ="select * from t_class_activity as ca ,t_class_info as cl,t_album as al "
				+ "where ca.class_id=cl.class_id and ca.album_id=al.album_id limit ?,?;";
		List<ClassActivity> list=new ArrayList<ClassActivity>();
		list =JdbcUtil.query(ClassActivity.class,sql,(pageNo-1)*10,pageSize);
		return list;
	}
	
	/**
	 * 查找所有的创建时间
	 * @param classActivityId班级活动编号
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<ClassActivity> findAllCreateTime(Integer classActivityId) throws Exception {
		String sql ="SELECT create_time FROM t_class_activity WHERE class_activity_id=?";
		return JdbcUtil.query(ClassActivity.class,sql,classActivityId);
	}
	
	/**
	 * 查询单一实体
	 * 通过id查询
	 */
	public ClassActivity findByKey(Object Key) throws Exception {
		String sql ="SELECT * FROM t_class_activity WHERE class_activity_id = ?";
		return (ClassActivity) JdbcUtil.uniqueBean(ClassActivity.class,sql,Key);
	}
	
	/**
	 * String 对应  数据库 列名（comment)
	 * Object 对应相应列名值
	 * 返回的是所查找的类中的数据。方便EXCEL导出
	 */
	@Override
	public List<Map<String,Object>> getMapList() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	public long getPages(int classId, int pageSize) throws Exception {
		String sql="SELECT COUNT(*) FROM t_class_activity AS ca ,t_class_info AS cl,"
				+ "t_album AS al WHERE ca.class_id=cl.class_id AND ca.album_id=al.album_id "
				+ "AND ca.class_id=?";
		long count=(long) JdbcUtil.uniqueValue(sql, 1, classId);
		return count%pageSize==0?count/pageSize:count/pageSize+1;
	}
	@SuppressWarnings("unchecked")
	@Override
	public List<ClassActivity> findAll(Object Key, int pageNo, int pageSize) throws Exception {
		String sql ="SELECT * FROM t_class_activity AS ca ,t_class_info AS cl,t_album AS al ";
		sql+="WHERE ca.class_id=cl.class_id AND ca.album_id=al.album_id AND ca.class_id=? limit ?,?";
		List<ClassActivity> list=new ArrayList<ClassActivity>();
		list =JdbcUtil.query(ClassActivity.class,sql,Key,(pageNo-1)*pageSize,pageSize);
		return list;
	}

	public int changeState(int id,int state) throws Exception {
		String sql = "update t_class_activity set activity_state = ? where class_activity_id = ?";
		return JdbcUtil.update(sql, state, id);
	}
	/**
	 * 邹磊
	 * 查询所有活动内容		链表		班级信息表 
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<ClassActivity> findAllInfo(Object key,Integer pageNo, Integer pageSize) throws Exception{
		String sql ="SELECT ta.*,cl.class_name FROM t_class_activity ta,t_class_info  cl  WHERE ta.class_id=cl.class_id AND cl.class_tch_id=? LIMIT ?,?";
		return JdbcUtil.query(ClassActivity.class,sql,key,pageNo,pageSize);
	}
	/**
	 * 邹磊
	 * 根据辅导员id查询所有活动条目
	 * @param tchId
	 * @return
	 * @throws Exception
	 */
	public Long findAllCount(Integer tchId) throws Exception{
		String sql ="SELECT COUNT(cl.class_name) FROM t_class_activity ta,t_class_info  cl  WHERE ta.class_id=cl.class_id AND cl.class_tch_id=?";
		return (Long) JdbcUtil.uniqueValue(sql, 1,tchId );
	}
	/**
	 * 邹磊
	 * 查询单条活动
	 * @param aid
	 * @return
	 * @throws Exception
	 */
	public ClassActivity findByAid(Integer aid) throws Exception{
		String sql = "SELECT * FROM  t_class_activity  WHERE  class_activity_id=?";
		return (ClassActivity) JdbcUtil.uniqueBean(ClassActivity.class, sql, aid);
	}
	
	/**
	 * 邹磊
	 * 根据班级名称查询活动  链表
	 * @param tchId
	 * @param cName
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<ClassActivity> findByName(Integer tchId,String cName,Integer pageNo, Integer pageSize) throws Exception{
		String sql ="SELECT ta.*,cl.class_name FROM t_class_activity ta,t_class_info  cl   ";
				sql+= "WHERE ta.class_id=cl.class_id AND cl.class_tch_id=? AND cl.class_name=? LIMIT ?,?";
		return JdbcUtil.query(ClassActivity.class,sql,tchId,cName,pageNo,pageSize);
	}
	
	/**
	 * 邹磊
	 * 根据辅导员id和班级名称查询所有活动条目
	 * @param tchId
	 * @return
	 * @throws Exception
	 */
	public Long findByNameCount(int tchId,String cName) throws Exception{
		String sql ="SELECT COUNT(*) FROM t_class_activity ta,t_class_info  cl  ";
		sql+=" WHERE ta.class_id=cl.class_id AND cl.class_tch_id=? AND cl.class_name=? ";
		return (Long) JdbcUtil.uniqueValue(sql, 1,tchId,cName );
	}
	/**
	 * 子查询根据stu_id
	 * 邹磊
	 */
	@SuppressWarnings("unchecked")
	public List<ClassActivity> findAllByStuid(Object Key, int pageNo, int pageSize) throws Exception {
		String sql ="SELECT * FROM t_class_activity WHERE class_id=( ";
		sql+="SELECT class_id FROM t_class_info WHERE class_name=(SELECT stu_class_name FROM t_student WHERE stu_id=?)) limit ?,?";
		List<ClassActivity> list=new ArrayList<ClassActivity>();
		list =JdbcUtil.query(ClassActivity.class,sql,Key,pageNo,pageSize);
		return list;
	}
}
